﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Dynamic;
using KingRoad.EPM.Web.Areas.SafetyConstruction.ViewModels;
using KingRoad.EPM.SafetyManagementEntity.Model;
using KingRoad.EPM.Web.Areas.SafetyConstruction.SafetyConstructionFramework.Common;
using System.IO;
using System.Data;

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System.Text;
using NPOI.SS.Util;

namespace KingRoad.EPM.Web.Areas.SafetyConstruction.Controllers
{
    public class ScmSpecialPersonnelController : BaseController
    {
        public const string RYGL_SGRY = "RYGL_TZZYRY";
        //
        // GET: /ScmSpecialPersonnel/
        /// <summary>
        /// 特种作业人员台账-首页
        /// </summary>
        /// <returns></returns>
        public ActionResult SpecialPersonnelIndex()
        {
            var model = new ScmSpecialpersonnelViewModel();
            //施工单位列表
            model.TheUnitList = GetUnitListByType(1); ;
            return View(model);
        }
        /// <summary>
        /// 特种作业人员台账-列表
        /// </summary>
        /// <returns></returns>
        public ActionResult SpecialPersonnelList(string Section)
        {
            var puu = GetUnitInfoByCode(Section);
            ViewBag.Section = Section;
            ViewBag.danwei = puu.UNIT_NAME;
            //return View();
            int totalRecord = 0;
            //当前页码
            ViewBag.pageNum = 1;
            //每页多少条
            ViewBag.numPerPage = 20;
            var specialpersonnel = ScmSpecialpersonnelUIRule.GetAllCostplanmainForPage(ViewBag.pageNum, ViewBag.numPerPage, "Sp_timestamp", out totalRecord, "Sp_Section='" + Section + "'");

            int tempTotalRecord = 0;
            var allList = ScmSpecialpersonnelUIRule.GetAllCostplanmainForPage(1, ALL_RECORD_ITEMS, "Sp_timestamp", out tempTotalRecord, "Sp_Section='" + Section + "'");
            SetSectionValue(RYGL_SGRY, allList);

            //总条数
            ViewBag.totalcount = totalRecord;
            //总页数
            ViewBag.pagenumshown = totalRecord % ViewBag.numPerPage == 0 ? totalRecord / ViewBag.numPerPage : totalRecord / ViewBag.numPerPage + 1;
            return View(new ScmSpecialpersonnelViewModel {TheSpecialpersonnelList=specialpersonnel });
        }
        /// <summary>
        /// 特种作业人员台账-列表(分页提交)
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public ActionResult SplitPage()
        {
            int totalRecord = 0;
            //当前页码
            ViewBag.pageNum = Convert.ToInt32(Request.Form["pageNum"]);
            //每页多少条
            ViewBag.numPerPage = Convert.ToInt32(Request.Form["numPerPage"]);
            ViewBag.iput_name = Request.Form["iput_name"];
            ViewBag.iput_work = Request.Form["iput_work"];
            ViewBag.Section = Request.Form["input_section"];
            var puu = GetUnitInfoByCode(ViewBag.Section);
            ViewBag.danwei = puu.UNIT_NAME;
            string sql = " 1=1 ";
            if (ViewBag.iput_name != "")
                sql += " and Sp_Name like '%" + ViewBag.iput_name + "%' ";
            if (ViewBag.iput_work != "")
                sql += " and Sp_Work like '%" + ViewBag.iput_work + "%' ";
            if (ViewBag.Section != "")
                sql += " and Sp_Section= '" + ViewBag.Section + "' ";

            var SpecialPersonnelList = ScmSpecialpersonnelUIRule.GetAllCostplanmainForPage(ViewBag.pageNum, ViewBag.numPerPage, "Sp_timestamp", out totalRecord, sql);

            int tempTotalRecord = 0;
            var allList = ScmSpecialpersonnelUIRule.GetAllCostplanmainForPage(1, ALL_RECORD_ITEMS, "Sp_timestamp", out tempTotalRecord, sql);
            SetSectionValue(RYGL_SGRY, allList);
            
            //总条数
            ViewBag.totalcount = totalRecord;
            //总页数
            ViewBag.pagenumshown = totalRecord % ViewBag.numPerPage == 0 ? totalRecord / ViewBag.numPerPage : totalRecord / ViewBag.numPerPage + 1;
            return View("SpecialPersonnelList", new ScmSpecialpersonnelViewModel { TheSpecialpersonnelList = SpecialPersonnelList });
        }
        /// <summary>
        /// 特种作业人员台账-添加
        /// </summary>
        /// <returns></returns>
        public ActionResult SpecialPersonnelAdd(string Section)
        {
            var model = new ScmSpecialpersonnelViewModel();
            model.TheSpecialpersonnelInfo = new ScmSpecialpersonnelInfo();
            model.TheSpecialpersonnelInfo.Sp_ID = Guid.NewGuid().ToString();
            model.TheSpecialpersonnelInfo.Sp_Section = Section;
            ProjectUnitInfo uInfo = GetUnitInfoByCode(Section);
            model.TheSpecialpersonnelInfo.Sp_ConstructionID = uInfo.UNIT_ID;
            model.TheSpecialpersonnelInfo.Sp_ConstructionName = uInfo.UNIT_NAME;

            List<SelectListItem> list = new List<SelectListItem>();
            list.Add(new SelectListItem {Text="男",Value="男" });
            list.Add(new SelectListItem {Text="女",Value="女" });
            ViewBag.list = list;
            return View(model);
        }

        /// <summary>
        /// 重点建设项目安全生产台账-添加 
        /// 功能：执行添加操作
        /// </summary>
        /// <returns></returns>
        public JsonResult SpecialPersonnelAddData(ScmSpecialpersonnelViewModel vm)
        {
            var isScuess = TryCatchCommand.TryExec(() =>
            {
                vm.TheSpecialpersonnelInfo.Sp_OperateUserID = GetLoginUserId;              //操作人ID及名称 添加权限时赋值
                vm.TheSpecialpersonnelInfo.Sp_OperateUserName = GetLoginUserRealName;
                vm.TheSpecialpersonnelInfo.Sp_OperateTime = DateTime.Now;
                int specialpersonnel = ScmSpecialpersonnelUIRule.AddSpecialpersonnel(vm.TheSpecialpersonnelInfo);
            });

            dynamic jsonReturn = new ExpandoObject();

            //保存成功
            if (isScuess)
            {
                jsonReturn = new
                {
                    statusCode = "200",
                    message = "添加成功",
                    navTabId = "SafetyConstruction.ScmSpecialPersonnel.SpecialPersonnelList",
                    rel = "",
                    callbackType = "",
                    forwardUrl = "",
                    confirmMsg = ""
                };
            }
            else
            {
                jsonReturn = new
                {
                    statusCode = "300",
                    message = "添加失败",
                };
            }

            return new JsonResult()
            {
                Data = jsonReturn
            };
        }
        /// <summary>
        /// 特种作业人员台账-删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public JsonResult SpecialPersonnelDelete(string id)
        {
            var isScuess = TryCatchCommand.TryExec(() =>
            {
                int specialpersonnel = ScmSpecialpersonnelUIRule.DeleteSpecialpersonnel(id);
            });
            dynamic jsonReturn = new ExpandoObject();
            //删除成功
            if (isScuess)
            {
                jsonReturn = new
                {
                    statusCode = "200",
                    message = "删除成功",
                    navTabId = "SafetyConstruction.ScmSpecialPersonnel.SpecialPersonnelList",
                    rel = "",
                    callbackType = "",
                    forwardUrl = "",
                    confirmMsg = ""
                };
            }
            else
            {
                jsonReturn = new
                {
                    statusCode = "300",
                    message = "删除失败",
                };
            }
            return new JsonResult()
            {
                Data = jsonReturn
            };
        }
        /// <summary>
        /// 特种作业人员台账-批量删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public JsonResult SpecialPersonnelMultiDelete()
        {
            dynamic jsonReturn = new ExpandoObject();
            var ids = base.GetRequestFormValue<string>("ids");
            var isScuess = TryCatchCommand.TryExec(() =>
            {
                int specialpersonnel = ScmSpecialpersonnelUIRule.DeleteSpecialpersonnelMulti(ids);
            });

            //保存成功
            if (true)
            {
                jsonReturn = new
                {
                    statusCode = "200",
                    message = "删除成功",
                    navTabId = "SafetyConstruction.ScmSpecialPersonnel.SpecialPersonnelList",
                    rel = "",
                    callbackType = "",
                    forwardUrl = "",
                    confirmMsg = ""
                };
            }
            else
            {
                jsonReturn = new
                {
                    statusCode = "300",
                    message = "删除失败",
                };
            }
            return new JsonResult()
            {
                Data = jsonReturn
            };
        }
        /// <summary>
        /// 特种作业人员台账——编辑
        /// 功能：根据id加载相对应数据
        /// </summary>
        /// <returns></returns>
        public ActionResult SpecialPersonnelEdit(Guid id)
        {
            var SpecialPersonnelInfo = ScmSpecialpersonnelUIRule.GetSpecialpersonnelByID(id);
            List<SelectListItem> list = new List<SelectListItem>();
            list.Add(new SelectListItem { Text = "男", Value = "男" });
            list.Add(new SelectListItem { Text = "女", Value = "女" });
            ViewBag.list = list;
            return View(new ScmSpecialpersonnelViewModel { TheSpecialpersonnelInfo = SpecialPersonnelInfo });
            //return View("SafeProductionAdd");
        }
        /// <summary>
        /// 特种作业人员台账-编辑（保存工作） 
        /// 功能：执行保存数据
        /// </summary>
        /// <returns></returns>
        public JsonResult SpecialPersonnelEditData(ScmSpecialpersonnelViewModel vm)
        {
            var isScuess = TryCatchCommand.TryExec(() =>
            {
                vm.TheSpecialpersonnelInfo.Sp_OperateUserID = GetLoginUserId;              //操作人ID及名称 添加权限时赋值
                vm.TheSpecialpersonnelInfo.Sp_OperateUserName = GetLoginUserRealName;
                vm.TheSpecialpersonnelInfo.Sp_OperateTime = DateTime.Now;
                int safe = ScmSpecialpersonnelUIRule.UpdateSpecialpersonnel(vm.TheSpecialpersonnelInfo);
            });

            dynamic jsonReturn = new ExpandoObject();

            //保存成功
            if (isScuess)
            {
                jsonReturn = new
                {
                    statusCode = "200",
                    message = "更新成功",
                    navTabId = "SafetyConstruction.ScmSpecialPersonnel.SpecialPersonnelList",
                    rel = "",
                    callbackType = "",
                    forwardUrl = "",
                    confirmMsg = ""
                };
            }
            else
            {
                jsonReturn = new
                {
                    statusCode = "300",
                    message = "更新失败",
                };
            }

            return new JsonResult()
            {
                Data = jsonReturn
            };
        }
        /// <summary>
        /// 特种作业人员台账——查看
        /// </summary>
        /// <returns></returns>
        public ActionResult SpecialPersonnelDetail(Guid id)
        {
            var SpecialPersonnelInfo = ScmSpecialpersonnelUIRule.GetSpecialpersonnelByID(id);
            return View(new ScmSpecialpersonnelViewModel { TheSpecialpersonnelInfo = SpecialPersonnelInfo });
        }
        /// <summary>
        /// Excel导出
        /// </summary>
        /// <returns></returns>
        public FileResult Excel(string Section, string danwei)
        {
            //获取list数据           

            //var list = ScmSpecialpersonnelUIRule.GetAllSpecialpersonnelBySection(" Sp_Section = '" + Section + "'");
            var list = GetSessionValue<List<ScmSpecialpersonnelInfo>>(RYGL_SGRY);
            IWorkbook book = new HSSFWorkbook();
            //添加一个sheet
            ISheet sheet1 = book.CreateSheet("Sheet1");
            //列宽度
            sheet1.SetColumnWidth(0, 5 * 256);
            sheet1.SetColumnWidth(1, 15 * 256);
            sheet1.SetColumnWidth(2, 15 * 256);
            sheet1.SetColumnWidth(3, 13 * 256);
            sheet1.SetColumnWidth(4, 15 * 256);
            sheet1.SetColumnWidth(5, 13 * 256);
            sheet1.SetColumnWidth(6, 30 * 256);
            sheet1.SetColumnWidth(7, 13 * 256);
            sheet1.SetColumnWidth(8, 13 * 256);

            //设置第一行
            IRow row0 = sheet1.CreateRow(0);
            //行高
            row0.Height = 30 * 20;
            //合并单元格
            sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 8));
            //创建单元格
            CreateCell(row0, 0, Getcellstyle(book, "center", false, 14, 300), Section + "标特种作业人员台账");
            //第二行
            IRow row1 = sheet1.CreateRow(1);
            sheet1.AddMergedRegion(new CellRangeAddress(1, 1, 0, 4));
            sheet1.AddMergedRegion(new CellRangeAddress(1, 1, 5, 8));
            CreateCell(row1, 0, Getcellstyle(book, "left", false), "标段：" + Section);
            CreateCell(row1, 5, Getcellstyle(book, "right", false), "施工单位：" + danwei);
            //第三行
            IRow row2 = sheet1.CreateRow(2);

            CreateCell(row2, 0, Getcellstyle(book, "center", true, 11, 600), "序号");
            CreateCell(row2, 1, Getcellstyle(book, "center", true, 11, 600), "姓名");
            CreateCell(row2, 2, Getcellstyle(book, "center", true, 11, 600), "性别");
            CreateCell(row2, 3, Getcellstyle(book, "center", true, 11, 600), "工种");
            CreateCell(row2, 4, Getcellstyle(book, "center", true, 11, 600), "发证时间");
            CreateCell(row2, 5, Getcellstyle(book, "center", true, 11, 600), "有效期");
            CreateCell(row2, 6, Getcellstyle(book, "center", true, 11, 600), "证书编号");
            CreateCell(row2, 7, Getcellstyle(book, "center", true, 11, 600), "进场时间");
            CreateCell(row2, 8, Getcellstyle(book, "center", true, 11, 600), "退场时间");



            //将数据逐步写入sheet1各个行
            for (int i = 0; i < list.Count; i++)
            {
                IRow rowtemp = sheet1.CreateRow(i + 3);
                CreateCell(rowtemp, 0, Getcellstyle(book), (i + 1).ToString());
                CreateCell(rowtemp, 1, Getcellstyle(book), list[i].Sp_Name);
                CreateCell(rowtemp, 2, Getcellstyle(book), list[i].Sp_Sex);
                CreateCell(rowtemp, 3, Getcellstyle(book), list[i].Sp_Work);
                CreateCell(rowtemp, 4, Getcellstyle(book), list[i].Sp_CertificateDate==null?"":Convert.ToDateTime(list[i].Sp_CertificateDate).ToString("yyyy-MM-dd"));
                CreateCell(rowtemp, 5, Getcellstyle(book), list[i].Sp_ValidityDate == null ? "" : Convert.ToDateTime(list[i].Sp_ValidityDate).ToString("yyyy-MM-dd"));
                CreateCell(rowtemp, 6, Getcellstyle(book), list[i].Sp_CertificateNumb);
                CreateCell(rowtemp, 7, Getcellstyle(book), list[i].Sp_EnterDate == null ? "" : Convert.ToDateTime(list[i].Sp_EnterDate).ToString("yyyy-MM-dd"));
                CreateCell(rowtemp, 8, Getcellstyle(book), list[i].Sp_ExitDate == null ? "" : Convert.ToDateTime(list[i].Sp_ExitDate).ToString("yyyy-MM-dd"));

            }
            // 写入到客户端 
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            book.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            string fileName = HttpUtility.UrlEncode("特种作业人员台账.xls", Encoding.GetEncoding("UTF-8"));
            return File(ms, "application/vnd.ms-excel", fileName);
         
        }
        #region Excel导入
        /// <summary>
        /// Excel导入
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public JsonResult ImportExcelFile(HttpPostedFileBase FileData)
        {
            string infor = "";
            var isScuess = TryCatchCommand.TryExec(() =>
            {
                if (FileData != null)
                {
                    string tempFileName = FileData.FileName;
                    string fileExt = Path.GetExtension(tempFileName).ToLower();
                    string fileName = Guid.NewGuid().ToString() + fileExt;
                    string tempPath = "/TempleterFilePath";
                    if (!tempPath.EndsWith("/"))
                        tempPath += "/";
                    tempPath = Server.MapPath(tempPath);
                    if (!Directory.Exists(tempPath))
                        Directory.CreateDirectory(tempPath);
                    tempPath = Path.Combine(tempPath, fileName);
                    FileData.SaveAs(tempPath);
                    if (string.IsNullOrWhiteSpace(tempPath) || !System.IO.File.Exists(tempPath))
                    {
                        throw new Exception("导入文件不存在");
                    }

                    string Section = Request.QueryString["Section"];
                    HSSFWorkbook hssfworkbook;
                    //读取文件流
                    using (FileStream fs = System.IO.File.OpenRead(tempPath))
                    {
                        hssfworkbook = new HSSFWorkbook(fs);
                        //获取第一个工作簿
                        NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
                        {
                            var ret = ExcelImportValidation.Validation<ScmSpecialpersonnelInfo>(sheet);
                            if (ret.Item1)
                            {
                                //获取第一行
                                IRow headerRow = sheet.GetRow(0);
                                //列数
                                int cellCount = headerRow.LastCellNum;
                                //行数
                                int rowCount = sheet.LastRowNum;
                                //循环行
                                for (int i = 1; i <= rowCount; i++)
                                {
                                    IRow row = sheet.GetRow(i);
                                    //特种作业人员台账
                                    ScmSpecialpersonnelInfo info = new ScmSpecialpersonnelInfo();
                                    info.Sp_ID = Guid.NewGuid().ToString();
                                    info.Sp_Section = Section;
                                    ProjectUnitInfo uInfo = GetUnitInfoByCode(Section);
                                    info.Sp_ConstructionID = uInfo.UNIT_ID;
                                    info.Sp_ConstructionName = uInfo.UNIT_NAME;
                                    info.Sp_OperateTime = DateTime.Now;
                                    //操作人ID及名称 添加权限时赋值
                                    info.Sp_OperateUserID = GetLoginUserId;
                                    info.Sp_OperateUserName = GetLoginUserRealName;
                                    //读取该行信息
                                    info.Sp_Name = GetCellValue(row.GetCell(0));
                                    info.Sp_Sex = GetCellValue(row.GetCell(1));
                                    info.Sp_Work = GetCellValue(row.GetCell(2));
                                    info.Sp_CertificateDate = Convert.ToDateTime(GetCellValue(row.GetCell(3)));
                                    info.Sp_ValidityDate = Convert.ToDateTime(GetCellValue(row.GetCell(4)));
                                    info.Sp_CertificateNumb = GetCellValue(row.GetCell(5));
                                    info.Sp_EnterDate = Convert.ToDateTime(GetCellValue(row.GetCell(6)));
                                    info.Sp_ExitDate = Convert.ToDateTime(GetCellValue(row.GetCell(7)));

                                    //向数据库插入数据
                                    ScmSpecialpersonnelUIRule.AddSpecialpersonnel(info);
                                }
                            }
                            else
                            {
                                infor = ret.Item2;
                            }
                        }
                    }
                    //删除临时文件
                    System.IO.File.Delete(tempPath);
                }
               
            });
            dynamic jsonReturn = new ExpandoObject();
            //保存成功
            if (isScuess)
            {
                if (infor == "")
                {
                    jsonReturn = new
                    {
                        statusCode = "200",
                        message = "导入成功",
                        navTabId = "",
                        rel = "",
                        callbackType = "",
                        forwardUrl = "",
                        confirmMsg = ""
                    };
                }
                else
                {
                    jsonReturn = new
                    {
                        statusCode = "300",
                        message = "导入失败!  " + infor,
                    };
                }
            }
            else
            {
                jsonReturn = new
                {
                    statusCode = "300",
                    message = "导入失败",
                };
            }
            return new JsonResult()
            {
                Data = jsonReturn
            };
        }
        //public JsonResult ImportExcelFile(int[] datas)
        //{
        //    string infor = "";
        //    var isScuess = TryCatchCommand.TryExec(() =>
        //    {
        //        string Section = Request.Form["Section"];
        //        HSSFWorkbook hssfworkbook;
        //        var bytes = datas.ToList().ConvertAll(x => (byte)x).ToArray();
        //        //读取文件流
        //        using (Stream file = new MemoryStream(bytes))
        //        {
        //            hssfworkbook = new HSSFWorkbook(file);
        //        }
        //        //获取第一个工作簿
        //        NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
        //        {
        //            var ret = ExcelImportValidation.Validation<ScmSpecialpersonnelInfo>(sheet);
        //             if (ret.Item1)
        //             {
        //                 //获取第一行
        //                 IRow headerRow = sheet.GetRow(0);
        //                 //列数
        //                 int cellCount = headerRow.LastCellNum;
        //                 //行数
        //                 int rowCount = sheet.LastRowNum;
        //                 //循环行
        //                 for (int i = 1; i <= rowCount; i++)
        //                 {
        //                     IRow row = sheet.GetRow(i);
        //                     //特种作业人员台账
        //                     ScmSpecialpersonnelInfo info = new ScmSpecialpersonnelInfo();
        //                     info.Sp_ID = Guid.NewGuid().ToString();
        //                     info.Sp_Section = Section;
        //                     ProjectUnitInfo uInfo = GetUnitInfoByCode(Section);
        //                     info.Sp_ConstructionID = uInfo.UNIT_ID;
        //                     info.Sp_ConstructionName = uInfo.UNIT_NAME;
        //                     info.Sp_OperateTime = DateTime.Now;
        //                     //操作人ID及名称 添加权限时赋值
        //                     info.Sp_OperateUserID = GetLoginUserId;
        //                     info.Sp_OperateUserName = GetLoginUserRealName;
        //                     //读取该行信息
        //                     info.Sp_Name = GetCellValue(row.GetCell(0));
        //                     info.Sp_Sex = GetCellValue(row.GetCell(1));
        //                     info.Sp_Work = GetCellValue(row.GetCell(2));
        //                     info.Sp_CertificateDate = Convert.ToDateTime(GetCellValue(row.GetCell(3)));
        //                     info.Sp_ValidityDate = Convert.ToDateTime(GetCellValue(row.GetCell(4)));
        //                     info.Sp_CertificateNumb = GetCellValue(row.GetCell(5));
        //                     info.Sp_EnterDate = Convert.ToDateTime(GetCellValue(row.GetCell(6)));
        //                     info.Sp_ExitDate = Convert.ToDateTime(GetCellValue(row.GetCell(7)));

        //                     //向数据库插入数据
        //                     ScmSpecialpersonnelUIRule.AddSpecialpersonnel(info);
        //                 }
        //             }
        //             else
        //             {
        //                 infor = ret.Item2;
        //             }
        //        }
        //    });
        //    dynamic jsonReturn = new ExpandoObject();
        //    //保存成功
        //    if (isScuess)
        //    {
        //        if (infor == "")
        //        {
        //            jsonReturn = new
        //            {
        //                statusCode = "200",
        //                message = "导入成功",
        //                navTabId = "",
        //                rel = "",
        //                callbackType = "",
        //                forwardUrl = "",
        //                confirmMsg = ""
        //            };
        //        }
        //        else
        //        {
        //            jsonReturn = new
        //            {
        //                statusCode = "300",
        //                message = "导入失败!  " + infor,
        //            };
        //        }
        //    }
        //    else
        //    {
        //        jsonReturn = new
        //        {
        //            statusCode = "300",
        //            message = "导入失败",
        //        };
        //    }
        //    return new JsonResult()
        //    {
        //        Data = jsonReturn
        //    };
        //}

        
        #endregion

        /// <summary>
        /// 特种作业人员台账-组合查询 
        /// </summary>
        /// <param name="vm"></param>
        /// <returns></returns>
        public ActionResult SpecialPersonnelSearch(ScmSpecialpersonnelViewModel vm)
        {
            //var type = GetRequestFormValue<string>("pageType");
            //PageType(type);

            var sqlStr = GetSearchSql(vm);
            int totalRecord = 0;
            var SpecialPersonnelList = ScmSpecialpersonnelUIRule.GetSpecialpersonnelForSearch(1, 20, "Sp_EnterDate", out totalRecord, sqlStr);

            ViewBag.totalcount = totalRecord;
            ViewBag.pagenumshown = totalRecord % 20 == 0 ? totalRecord / 20 : totalRecord / 20 + 1;
            return View("SpecialPersonnelList", new ScmSpecialpersonnelViewModel
            {
                TheSpecialpersonnelList = SpecialPersonnelList
            }
            );
        }

        /// <summary>
        /// 拼接查询语句条件语句
        /// </summary>
        /// <param name="vm"></param>
        /// <returns></returns>
        public string GetSearchSql(ScmSpecialpersonnelViewModel vm)
        {
            var sqlStr = String.Format(" 1=1 ");
            if (vm == null) return sqlStr;

            if (!String.IsNullOrEmpty(vm.TheSpecialpersonnelInfo.Sp_Name))
                sqlStr += String.Format(" AND Sp_Name LIKE '%{0}%' ", vm.TheSpecialpersonnelInfo.Sp_Name);
            if (!String.IsNullOrEmpty(vm.TheSpecialpersonnelInfo.Sp_Work))
                sqlStr += String.Format("AND Sp_Work LIKE '%{0}%' ", vm.TheSpecialpersonnelInfo.Sp_Work);
            return sqlStr;
        }

       
    }
}
